package com.cs.paoyer.administration.business.entity;

//import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
//import java.util.HashMap;
//import java.util.Vector;
//import java.sql.PreparedStatement;
//import java.util.Iterator;

import com.cs.paoyer.administration.business.model.AuthorizedByBranchVO;
//import com.cs.paoyer.common.util.Encrypter;
//import java.security.NoSuchAlgorithmException;
//
//import com.cs.paoyer.common.exception.UserException;
//import com.cs.paoyer.common.exception.SystemException;

/**
 * DAO para crear, consultar y actualizar registros en la vista VFD_AUTORIZADOS_X_SURCURSAL
 * 
 * @author Rene.Rangel@consultsoft.com
 * @version 1.0
 */

public class AuthorizedByBranchDAO 
{
  /**
   * Conexion a la base de datos.
   */
  private Connection conn = null;

  /**
   * Constructor que inicializa la conexion con la base de datos.
   * @param conn Conexion con la base de datos.
   */
  
  public AuthorizedByBranchDAO(Connection conn){
    this.conn = conn;
  }

/**
  * Consulta un grupo de registros de la tabla TCS_OPCION a partir
  * de las condiciones que vienen en el Value Object
  *
  * @param OptionVO      Value Object con las condiciones a tener encuenta.
  * 
  * @return Collection   Arreglo de registros retornados de la consulta.
  */  
    public Collection findAuthorizedBranchByCriterea(AuthorizedByBranchVO criterea) throws SQLException 
    {
    
    /**
     * Se consulta sobre la vista creada para este fin
     */
    
    
    String query = "SELECT ausu.* " +
    "FROM vfd_autorizados_x_sucursal ausu";

    String filters = "";
    // Filtro por Nombre de Sucursal
    if ( (criterea.getBranchName() != null) && !(criterea.getBranchName().equals("")) ) {
      if ( filters.equalsIgnoreCase("") ) {
        filters = filters + " UPPER(ausu.sucu_nombre) LIKE '%" + criterea.getBranchName().toUpperCase() + "%' ";
      }
      else {
        filters = filters + " AND UPPER(ausu.sucu_nombre) LIKE '%" + criterea.getBranchName().toUpperCase() + "%' ";
      }
    }

    // Filtro por Codigo Usuario de Sucursal
    if ( (criterea.getBranchCustomCode() != null) && !(criterea.getBranchCustomCode().intValue() == 0) ) {
      if ( filters.equalsIgnoreCase("") ) {
        filters = filters + " ausu.sucu_codusu = " + criterea.getBranchCustomCode();
      }
      else {
        filters = filters + " AND ausu.sucu_codusu = " + criterea.getBranchCustomCode();
      }
    }

    

    // Filtro por Codigo interno de Sucursal
    if ( (criterea.getBranchInternalCode() != null) && !(criterea.getBranchInternalCode().intValue() == 0) ) {
      if ( filters.equalsIgnoreCase("") ) {
        filters = filters + " ausu.sucu_codin = " + criterea.getBranchInternalCode();
      }
      else {
        filters = filters + " AND ausu.sucu_codin = " + criterea.getBranchInternalCode();
      }
    }

    
    // Filtro por Nombre de Autorizado
    if ( (criterea.getAuthorizedName() != null) && !(criterea.getAuthorizedName().equals("")) ) {
      if ( filters.equalsIgnoreCase("") ) {
        filters = filters + " ausu.autr_nombre = " + criterea.getAuthorizedName().toUpperCase();
      }
      else {
        filters = filters + " AND ausu.autr_nombre = " + criterea.getAuthorizedName().toUpperCase();
      }
    }

    // Filtro por Codigo Usuario de Autorizado
    if ( (criterea.getAuthorizedCustomCode() != null) && !(criterea.getAuthorizedCustomCode().intValue() == 0) ) {
      if ( filters.equalsIgnoreCase("") ) {
        filters = filters + " ausu.autr_codusu = " + criterea.getAuthorizedCustomCode();
      }
      else {
        filters = filters + " AND ausu.autr_codusu = " + criterea.getAuthorizedCustomCode();
      }
    }

    // Filtro por Codigo Interno de Autorizado
    if ( (criterea.getAuthorizedInternalCode() != null) && !(criterea.getAuthorizedInternalCode().intValue() == 0) ) {
      if ( filters.equalsIgnoreCase("") ) {
        filters = filters + " ausu.autr_codin = " + criterea.getAuthorizedInternalCode();
      }
      else {
        filters = filters + " AND ausu.autr_codin = " + criterea.getAuthorizedInternalCode();
      }
    }

    // Si hay filtros, entonces armar la clausula WHERE 
    if ( !filters.equals("") ) {
      query = query + " WHERE " + filters;
    }

    Statement stm = null;
    ResultSet rst = null;
    ArrayList authorizedBranchs = new ArrayList();
   
    stm = conn.createStatement();
    rst = stm.executeQuery(query);
    AuthorizedByBranchVO authorizedBranch = null;
    while ( rst.next() ) 
    {
        authorizedBranch = new AuthorizedByBranchVO();
        authorizedBranch.setBranchInternalCode(new Long(rst.getLong( "sucu_codin")));      
        authorizedBranch.setBranchCustomCode(new Short(rst.getShort( "sucu_codusu")));      
        authorizedBranch.setBranchName(rst.getString( "sucu_nombre"));      
        authorizedBranch.setBranchAddress(rst.getString( "sucu_direccion"));      
        authorizedBranch.setBranchPhoneNumber(rst.getString( "sucu_telefono"));      
        authorizedBranch.setActiveBranch(rst.getString( "sucu_activo"));      
        authorizedBranch.setBranchFax(rst.getString( "sucu_fax"));      
        authorizedBranch.setBranchPostOfficeBox(rst.getString( "sucu_apartado_aereo"));      
        authorizedBranch.setBranchCityCode(new Long(rst.getLong( "sucu_ciu_codigo")));      
        authorizedBranch.setBranchManager(rst.getString( "sucu_gerente"));      
        authorizedBranch.setAuthorizedInternalCode(new Long(rst.getLong( "autr_codin")));      
        authorizedBranch.setAuthorizedName(rst.getString( "autr_nombre"));      
        authorizedBranch.setAuthorizedRequests(rst.getString( "autr_solicita"));      
        authorizedBranch.setAuthorizedReceives(rst.getString( "autr_recibe"));      
        authorizedBranch.setActiveAuthorized(rst.getString( "autr_activo"));      
        authorizedBranch.setAuthorizedCustomCode(new Long(rst.getLong( "autr_codusu")));      
        authorizedBranchs.add(authorizedBranch);
        authorizedBranch = null;
   }
         
    
   stm.close();
       
   return authorizedBranchs;
  }
  
  
  

}